﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;

namespace AssetManagementSystem.Model.Dao.MSAccess {
    class AssetDAO : AssetManagementSystem.Model.IDao.IAssetDAO {

        //The MSAccess database connection to use in all data access operations.
        private OleDbConnection dbConn;

        /// <summary>
        /// Constructs a new AssetDAO using a MSAccess database connection string.
        /// </summary>
        /// <param name="connectionString">MSAccess database connection string</param>
        public AssetDAO(string connectionString) {
            //create new database connection
            this.dbConn = new OleDbConnection(connectionString);
            //test the new database connection
            try {
                //make sure the connection can be opened.
                dbConn.Open();
            } catch (Exception e) {
                throw e;
            } finally {
                //close connection if goes went wrong.
                dbConn.Close();
            }
        }

        //====================================================================
        //  CRUD METHODS - CREATE, READ, UPDATE, DELETE
        //====================================================================

        /// <summary>
        /// Create a new asset in the database, from an 'Asset' business object.
        /// </summary>
        /// <param name="asset">The asset object to be stored in the database</param>
        public void CreateAsset(Asset asset) {
            //Make sure the asset doesn't already exist
            if (AssetExists(asset.Id)) {
                throw new Exception("An asset with the id provided already exists, could not be saved.");
            } else {
                try {
                    //store the new asset
                    dbConn.Open();
                    OleDbCommand oleDbCmd = new OleDbCommand(
                        "INSERT INTO Asset ([ID], [AssetName], [GroupName], [Description]) " +
                        "VALUES(@id, @AssetName, @GroupName, @Description)",
                        dbConn);
                    oleDbCmd.Parameters.AddWithValue("@id", asset.Id);
                    oleDbCmd.Parameters.AddWithValue("@AssetName", asset.Name);
                    oleDbCmd.Parameters.AddWithValue("@GroupName", asset.GroupName);
                    oleDbCmd.Parameters.AddWithValue("@Description", asset.Description);
                    //make sure the database changed
                    if (oleDbCmd.ExecuteNonQuery() < 1) {
                        throw new Exception("Asset not stored. An unknown error occured");
                    }
                } catch (Exception e) {
                    throw e;
                } finally {
                    dbConn.Close();
                }

                //now store each of the assets additional properties.
                foreach (KeyValuePair<string, string> property in asset.Properties) {
                    StoreAssetProperty(asset.Id, property);
                }
            }
        }

        

        /// <summary>
        /// Retrieve a single asset from the database, using its ID as the PKey.
        /// </summary>
        /// <param name="id">The asset's id</param>
        /// <returns>The 'Asset' business object for the requested asset.</returns>
        public Asset GetAsset(string id) {
            Asset asset = null;
            try {
                dbConn.Open();
                OleDbDataAdapter dAdapter = new OleDbDataAdapter("SELECT * FROM Asset WHERE (ID = ?)", dbConn);
                dAdapter.SelectCommand.Parameters.AddWithValue("ID", id);
                DataTable dataTable = new DataTable();
                dAdapter.Fill(dataTable);
                //if more or less than 1 asset is returned for this id, throw an exception
                if (dataTable.Rows.Count != 1) {
                    throw new Exception(dataTable.Rows.Count.ToString() + " records found for asset " + id.ToString() + " expected 1");
                }
                //create an Asset object for the record returned
                foreach (DataRow assetRow in dataTable.Rows) {
                    asset = new Asset(
                        assetRow["ID"].ToString(),
                        assetRow["AssetName"].ToString(),
                        assetRow["GroupName"].ToString(),
                        assetRow["Description"].ToString(),
                        new Dictionary<string,string>());
                }
            } catch (Exception e) {
                throw e;
            } finally {
                dbConn.Close();
            }

            //Get the additional properties of the asset.
            asset.Properties = GetAssetProperties(asset.Id);

            //return the asset
            return asset;
        }

        /// <summary>
        /// Returns a collection of all assets found within the database.
        /// </summary>
        /// <returns>A collection of all assets found within the database.</returns>
        public ICollection<Asset> GetAllAssets() {
            ICollection<Asset> allAssets = new List<Asset>();
            try {
                dbConn.Open();
                OleDbDataAdapter dAdapter = new OleDbDataAdapter("SELECT * FROM Asset", dbConn);
                DataTable dataTable = new DataTable();
                dAdapter.Fill(dataTable);
                //create an Asset object for each record returned
                foreach (DataRow assetRow in dataTable.Rows) {
                    allAssets.Add(new Asset(
                        assetRow["ID"].ToString(),
                        assetRow["AssetName"].ToString(),
                        assetRow["GroupName"].ToString(),
                        assetRow["Description"].ToString(),
                        new Dictionary<string, string>()));
                }
            } catch (Exception e) {
                throw e;
            } finally {
                dbConn.Close();
            }

            //Get the additional properties of the assets.
            foreach (Asset asset in allAssets) {
                asset.Properties = GetAssetProperties(asset.Id);
            }

            //return the assets
            return allAssets;
        }

        /// <summary>
        /// Update the details of an asset already stored within the database.
        /// </summary>
        /// <param name="asset">The new details of the asset to update. ID must remain the same as it is the PKey.</param>
        public void UpdateAsset(Asset asset) {
            //Ensure student already exists
            if (!AssetExists(asset.Id)) {
                throw new Exception("Cannot update asset, existing details not found in database.");
            } else {
                try {
                    OleDbCommand oleDbCmd = new OleDbCommand("UPDATE [Asset] SET [AssetName] = ?, [GroupName] = ?, [Description] = ? WHERE ID = ?",
                        dbConn);
                    dbConn.Open();
                    oleDbCmd.Parameters.AddWithValue("AssetName", asset.Name);
                    oleDbCmd.Parameters.AddWithValue("GroupName", asset.GroupName);
                    oleDbCmd.Parameters.AddWithValue("Description", asset.Description);
                    oleDbCmd.Parameters.AddWithValue("ID", asset.Id);
                    if (oleDbCmd.ExecuteNonQuery() < 1) {
                        throw new Exception("Asset not updated. An unknown error occured");
                    }
                } catch (Exception e) {
                    throw e;
                } finally {
                    dbConn.Close();
                }
            }

            //now store each of the assets additional properties.
            foreach (KeyValuePair<string, string> property in asset.Properties) {
                StoreAssetProperty(asset.Id, property);
            }
        }

        /// <summary>
        /// Delete data for a single asset from the database.
        /// </summary>
        /// <param name="id">The asset id of the asset to be deleted.</param>
        public void DeleteAsset(Asset asset) {

            //start by deleting all the properties of the asset
            foreach (KeyValuePair<string, string> property in asset.Properties) {
                DeleteAssetProperty(asset.Id, property.Key);
            }

            //Ensure asset already exists
            if (!AssetExists(asset.Id)) {
                throw new Exception("Cannot delete asset, existing details not found in database.");
            } else {
                try {
                    OleDbCommand oleDbCmd = new OleDbCommand("DELETE * FROM Asset WHERE ID = ?", dbConn);
                    oleDbCmd.Parameters.AddWithValue("ID", asset.Id);
                    dbConn.Open();
                    if (oleDbCmd.ExecuteNonQuery() < 1) {
                        throw new Exception("Asset not deleted. An unknown error occured");
                    }
                } catch (Exception e) {
                    throw e;
                } finally {
                    dbConn.Close();
                }
            }
        }

        //====================================================================
        //  END - CRUD METHODS
        //====================================================================

        //====================================================================
        //  UTILITY METHODS - USEFUL WHEN PERFORMING OTHER OPERATIONS
        //====================================================================

        /// <summary>
        /// Returns true if an asset exists with the specified Asset Id, otherwise returns false.
        /// </summary>
        /// <param name="id">The asset id to check for</param>
        /// <returns>True if an asset exists with the specified Id, otherwise false.</returns>
        public bool AssetExists(string id) {
            try {
                dbConn.Open();
                OleDbDataAdapter dAdapter = new OleDbDataAdapter("SELECT * FROM Asset WHERE (ID = ?)", dbConn);
                dAdapter.SelectCommand.Parameters.AddWithValue("ID", id);
                DataTable dataTable = new DataTable();
                dAdapter.Fill(dataTable);
                //return true if at least one student was returned.
                return dataTable.Rows.Count > 0;
            } catch (Exception e) {
                throw e;
            } finally {
                dbConn.Close();
            }
        }

        /// <summary>
        /// Returns true if a property with the specified name exists for the specified asset
        /// </summary>
        /// <param name="assetId">The asset whos property is being found</param>
        /// <param name="name">The name of the property to find</param>
        /// <returns>True if a property with the specified name exists for the specified asset</returns>
        public bool PropertyExists(string assetId, string name) {
            try {
                dbConn.Open();
                OleDbDataAdapter dAdapter = new OleDbDataAdapter("SELECT * FROM AssetProperty WHERE AssetId = ? AND PropertyName = ?", dbConn);
                dAdapter.SelectCommand.Parameters.AddWithValue("AssetId", assetId);
                dAdapter.SelectCommand.Parameters.AddWithValue("PropertyName", name);
                DataTable dataTable = new DataTable();
                dAdapter.Fill(dataTable);
                //return true if at least one student was returned.
                return dataTable.Rows.Count > 0;
            } catch (Exception e) {
                throw e;
            } finally {
                dbConn.Close();
            }
        }

        /// <summary>
        /// Stores an asset property, creates a new property if one does not already exist, otherwise it updates the existing one.
        /// </summary>
        /// <param name="assetId">The id of the asset this property is for</param>
        /// <param name="property">The property to create or update</param>
        private void StoreAssetProperty(string assetId, KeyValuePair<string, string> property) {
            if (PropertyExists(assetId, property.Key)) {
                //update existing property
                try {
                    OleDbCommand oleDbCmd = new OleDbCommand("UPDATE [AssetProperty] SET [PropertyValue] = ? WHERE AssetId = ? AND PropertyName = ?",
                        dbConn);
                    dbConn.Open();
                    oleDbCmd.Parameters.AddWithValue("PropertyValue", property.Value);
                    oleDbCmd.Parameters.AddWithValue("AssetId", assetId);
                    oleDbCmd.Parameters.AddWithValue("PropertyName", property.Key);
                    if (oleDbCmd.ExecuteNonQuery() < 1) {
                        throw new Exception("Asset Property '" + property.Key + "' not updated. An unknown error occured");
                    }
                } catch (Exception e) {
                    throw e;
                } finally {
                    dbConn.Close();
                }
            } else {
                //create new property
                try {
                    //store the new asset
                    dbConn.Open();
                    OleDbCommand oleDbCmd = new OleDbCommand(
                        "INSERT INTO AssetProperty ([AssetId], [PropertyName], [PropertyValue]) " +
                        "VALUES(@AssetId, @PropertyName, @PropertyValue)",
                        dbConn);
                    oleDbCmd.Parameters.AddWithValue("@AssetId", assetId);
                    oleDbCmd.Parameters.AddWithValue("@PropertyName", property.Key);
                    oleDbCmd.Parameters.AddWithValue("@PropertyValue", property.Value);
                    //make sure the database changed
                    if (oleDbCmd.ExecuteNonQuery() < 1) {
                        throw new Exception("Asset Property '" + property.Key + "' not stored. An unknown error occured");
                    }
                } catch (Exception e) {
                    throw e;
                } finally {
                    dbConn.Close();
                }
            }
        }

        /// <summary>
        /// Gets all the properties of a specific asset.
        /// </summary>
        /// <param name="assetId">The asset whos properties are to be returned</param>
        /// <returns>The asset's properties</returns>
        private Dictionary<string, string> GetAssetProperties(string assetId) {
            Dictionary<string, string> properties = new Dictionary<string,string>();
            try {
                dbConn.Open();
                OleDbDataAdapter dAdapter = new OleDbDataAdapter("SELECT * FROM AssetProperty WHERE (AssetId = ?)", dbConn);
                dAdapter.SelectCommand.Parameters.AddWithValue("AssetId", assetId);
                DataTable dataTable = new DataTable();
                dAdapter.Fill(dataTable);
                //create a key value pair for each record returned
                foreach (DataRow assetRow in dataTable.Rows) {
                    properties.Add(
                        assetRow["PropertyName"].ToString(),
                        assetRow["PropertyValue"].ToString()
                        );
                }
            } catch (Exception e) {
                throw e;
            } finally {
                dbConn.Close();
            }
            return properties;
        }

        /// <summary>
        /// Delete asset properties for a single asset.
        /// </summary>
        /// <param name="assetId">The asset id of the asset whos property is to be deleted</param>
        /// <param name="name">The name of the property to be deleted</param>
        public void DeleteAssetProperty(string assetId, string name) {
            //Ensure asset property already exists
            if (!PropertyExists(assetId, name)) {
                //throw new Exception("Cannot delete property, existing details not found in database.");
            } else {
                try {
                    OleDbCommand oleDbCmd = new OleDbCommand("DELETE * FROM AssetProperty WHERE AssetId = ? AND PropertyName = ?", dbConn);
                    oleDbCmd.Parameters.AddWithValue("AssetId", assetId);
                    oleDbCmd.Parameters.AddWithValue("PropertyName", name);
                    dbConn.Open();
                    if (oleDbCmd.ExecuteNonQuery() < 1) {
                        //throw new Exception("Property not deleted. An unknown error occured");
                    }
                } catch (Exception e) {
                    throw e;
                } finally {
                    dbConn.Close();
                }
            }
        }

        //====================================================================
        //  END - UTILITY METHODS
        //====================================================================

    }
}
